Connected vs. Disconnected Architecture in ADO.NET

In ADO.NET, there are two types of architectures for accessing and managing data:

1. Connected Architecture (Uses DataReader)
2. Disconnected Architecture (Uses DataSet)

Connected Architecture (Using DataReader)

Best for: Fast, read-only access to data with an active database connection.

How it Works?

• Establishes a connection to the database.
• Uses SqlCommand and SqlDataReader to retrieve data.
• Reads data row-by-row (forward-only).
• The connection remains open while reading data.
• After reading, the connection must be closed manually.

Example: Using DataReader (Connected Mode)

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connStr = "Server=your_server;Database=your_db;Integrated Security=True;";

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open(); // Must keep connection open
            SqlCommand cmd = new SqlCommand("SELECT ID, Name FROM Employees", conn);
            SqlDataReader reader = cmd.ExecuteReader(); // Executes query

            while (reader.Read()) // Reads row by row (Forward-only)
            {
                Console.WriteLine($"ID: {reader["ID"]}, Name: {reader["Name"]}");
            }

            reader.Close(); // Close reader
        } // Connection automatically closes here due to 'using'
    }
}

Pros:

1. Fast and efficient for large datasets.
2. Uses less memory (does not store data in memory).
3. Best for read-only operations.

Cons:

1. Requires an active connection to read data.
2. Cannot modify or store data in memory.
3. Cannot navigate backward (forward-only).

Disconnected Architecture (Using DataSet)

Best for: Working with data offline without an active database connection.

How it Works?

• Uses SqlDataAdapter to fetch data without keeping the connection open.
• Stores data in a DataSet (in-memory storage).
• Allows sorting, filtering, and modifying data.
• Can update changes back to the database using SqlDataAdapter.Update().

Example: Using DataSet (Disconnected Mode)

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connStr = "Server=your_server;Database=your_db;Integrated Security=True;";
        DataSet ds = new DataSet(); // Stores data in-memory

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            string query = "SELECT ID, Name FROM Employees";
            SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
            adapter.Fill(ds, "Employees"); // Loads data into DataSet
        }

        // No active connection required after fetching data
        foreach (DataRow row in ds.Tables["Employees"].Rows)
        {
            Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}");
        }
    }
}

Pros:

1. No need to keep the connection open.
2. Can work with multiple tables (like an in-memory database).
3. Supports modifications, filtering, and sorting.
4. Can update data back to the database.

Cons:

1. Slower than DataReader (stores data in memory).
2. Uses more memory for large datasets.

Key Differences: Connected vs. Disconnected Architecture

When to Use Which?

Summary